﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Threading;
using DBMan.Common.Global;
using DBMan.Common.Helper;
using DBMan.Common.Helper.SQLServer;

namespace DBMan
{
    public partial class Deal
    {
        /// <summary>
        /// 数据库初始
        /// </summary>
        private void DBInit()
        {
            try
            {
                this.SQLServerDBInit();
                this.MySQLDBInit();
            }
            catch (Exception ex)
            {
                LogWrite.TxtExport_DateNow(LOG_ERR_FILE, $"DBMan.Deal DBInit Err:{ex.Message}");
            }
        }

        private void SQLServerDBInit()
        {
            try
            {
                if (this.DBType != 0)
                {
                    return;
                }
                var db = KnDSQLServerHelper.Init(this.DBConnStr);
                var strSQL = $@"
SELECT 
  name AS tblName
FROM 
  sys.objects 
WHERE 
   type = 'U' 
  and 
   name<>'TblArchive'
";
                var lt = db.QueryToDyList(strSQL).Select(p => new
                {
                    TblName = p.Get<string>("tblName")
                });
                foreach (var tn in lt)
                {
                    this.SQLServerDBInit(db, tn.TblName);
                }
            }
            catch (Exception ex)
            {
                LogWrite.TxtExport_DateNow(LOG_ERR_FILE, $"DBMan.Deal SQLServerDBInit Err:{ex.Message}");
            }
        }

        private bool SQLServerDBInit(KnDSQLServerHelper db, string tblName)
        {
            try
            {
                var strSQL = $@"select count(1) cnt from sysobjects where xtype = 'TR' and name='trKnD_Insert_{tblName}'";
                var dy = db.QueryToDyList(strSQL).First();
                var cnt = dy.Get<int>("cnt");
                if (cnt <= 0)
                {
                    strSQL = $@"
Create trigger [dbo].[trKnD_Insert_{tblName}] on [dbo].[{tblName}]
for insert
as
begin
  begin try  
    declare @field varchar(max),@sql nvarchar(max),@field2 varchar(max)
	select @field='',@sql=N'',@field2=''
    SELECT * into #
    FROM 
    (SELECT d.name AS 表名,
         a.colorder AS 字段序号,
         a.name AS 字段名,
         (case
        WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN
        '1'else '0' end) AS 标识, (case
        WHEN 
        (SELECT count(*)
        FROM sysobjects
        WHERE (name IN 
            (SELECT name
            FROM sysindexes
            WHERE (id = a.id)
                    AND (indid IN 
                (SELECT indid
                FROM sysindexkeys
                WHERE (id = a.id)
                        AND (colid IN 
                    (SELECT colid
                    FROM syscolumns
                    WHERE (id = a.id)
                            AND (name = a.name)) )))))
                            AND (xtype = 'PK'))>0 THEN
                        '1'
                        ELSE '0' end) AS 主键, b.name AS 类型, a.length AS 占用字节数, COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 长度, isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS 小数位数, (case
                        WHEN a.isnullable=1 THEN
                        '1'else '0' end) AS 允许空, REPLACE(REPLACE(isnull(e.text,''),'(',''),')','') AS 默认值, isnull(g.[value],'') AS 字段说明
                    FROM syscolumns a
                    LEFT JOIN systypes b
                        ON a.xtype=b.xusertype
                    INNER JOIN sysobjects d
                        ON a.id=d.id
                            AND d.xtype='U'
                            AND d.name<>'dtproperties'
                    LEFT JOIN syscomments e
                        ON a.cdefault=e.id
                    LEFT JOIN sys.extended_properties g
                        ON a.id=g.major_id
                            AND a.colid = g.minor_id ) ak
                WHERE ak.表名='{tblName}'
            AND ak.主键=1
     
	 if ((select count(1) from #)<=0)
	 begin
	   RETURN
	 end
	 select * into #2 from inserted
	 select
	  @field=COALESCE(@field +',', '') + ak.字段名,
	  @field2=COALESCE(@field2 +'+', '') + ('''◆'''+'+cast('+ak.字段名+' as varchar(max))')
	 from
	   # ak
     select @field2 = '''''' + @field2	 
	 select @sql=N'insert into TblArchive(TableName,Extend1,Summary) select ''{tblName}'' f0,'''+@field+''' f1,'+@field2 +' f2 from #2'
	 EXEC sp_executesql @sql	 
	 drop table #
	 drop table #2
  end try
  begin catch
    select error_number() as error_number ,  
     error_message() as error_message,  
     error_state() as error_state,  
     error_severity() as error_severity 
  end catch
end
";
                    db.Execute(strSQL);
                }
                //----
                strSQL = $@"select count(1) cnt from sysobjects where xtype = 'TR' and name='trKnD_Update_{tblName}'";
                dy = db.QueryToDyList(strSQL).First();
                cnt = dy.Get<int>("cnt");
                if (cnt <= 0)
                {
                    strSQL = $@"
Create trigger [dbo].[trKnD_Update_{tblName}] on [dbo].[{tblName}]
for update
as
begin
  begin try  
    declare @field varchar(max),@sql nvarchar(max),@field2 varchar(max)
	select @field='',@sql=N'',@field2=''
    SELECT * into #
    FROM 
    (SELECT d.name AS 表名,
         a.colorder AS 字段序号,
         a.name AS 字段名,
         (case
        WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN
        '1'else '0' end) AS 标识, (case
        WHEN 
        (SELECT count(*)
        FROM sysobjects
        WHERE (name IN 
            (SELECT name
            FROM sysindexes
            WHERE (id = a.id)
                    AND (indid IN 
                (SELECT indid
                FROM sysindexkeys
                WHERE (id = a.id)
                        AND (colid IN 
                    (SELECT colid
                    FROM syscolumns
                    WHERE (id = a.id)
                            AND (name = a.name)) )))))
                            AND (xtype = 'PK'))>0 THEN
                        '1'
                        ELSE '0' end) AS 主键, b.name AS 类型, a.length AS 占用字节数, COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 长度, isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS 小数位数, (case
                        WHEN a.isnullable=1 THEN
                        '1'else '0' end) AS 允许空, REPLACE(REPLACE(isnull(e.text,''),'(',''),')','') AS 默认值, isnull(g.[value],'') AS 字段说明
                    FROM syscolumns a
                    LEFT JOIN systypes b
                        ON a.xtype=b.xusertype
                    INNER JOIN sysobjects d
                        ON a.id=d.id
                            AND d.xtype='U'
                            AND d.name<>'dtproperties'
                    LEFT JOIN syscomments e
                        ON a.cdefault=e.id
                    LEFT JOIN sys.extended_properties g
                        ON a.id=g.major_id
                            AND a.colid = g.minor_id ) ak
                WHERE ak.表名='{tblName}'
            AND ak.主键=1
     
	 if ((select count(1) from #)<=0)
	 begin
	   RETURN
	 end
	 select * into #2 from inserted
	 select
	  @field=COALESCE(@field +',', '') + ak.字段名,
	  @field2=COALESCE(@field2 +'+', '') + ('''◆'''+'+cast('+ak.字段名+' as varchar(max))')
	 from
	   # ak
     select @field2 = '''''' + @field2	 
	 select @sql=N'insert into TblArchive(TableName,Extend1,Summary,Flag) select ''{tblName}'' f0,'''+@field+''' f1,'+@field2 +' f2,1 from #2'
	 EXEC sp_executesql @sql	 
	 drop table #
	 drop table #2
  end try
  begin catch
    select error_number() as error_number ,  
     error_message() as error_message,  
     error_state() as error_state,  
     error_severity() as error_severity 
  end catch
end
";
                    db.Execute(strSQL);
                }
                //----
                strSQL = $@"select count(1) cnt from sysobjects where xtype = 'TR' and name='trKnD_Delete_{tblName}'";
                dy = db.QueryToDyList(strSQL).First();
                cnt = dy.Get<int>("cnt");
                if (cnt <= 0)
                {
                    strSQL = $@"
Create trigger [dbo].[trKnD_Delete_{tblName}] on [dbo].[{tblName}]
for delete
as
begin
  begin try  
    declare @field varchar(max),@sql nvarchar(max),@field2 varchar(max)
	select @field='',@sql=N'',@field2=''
    SELECT * into #
    FROM 
    (SELECT d.name AS 表名,
         a.colorder AS 字段序号,
         a.name AS 字段名,
         (case
        WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN
        '1'else '0' end) AS 标识, (case
        WHEN 
        (SELECT count(*)
        FROM sysobjects
        WHERE (name IN 
            (SELECT name
            FROM sysindexes
            WHERE (id = a.id)
                    AND (indid IN 
                (SELECT indid
                FROM sysindexkeys
                WHERE (id = a.id)
                        AND (colid IN 
                    (SELECT colid
                    FROM syscolumns
                    WHERE (id = a.id)
                            AND (name = a.name)) )))))
                            AND (xtype = 'PK'))>0 THEN
                        '1'
                        ELSE '0' end) AS 主键, b.name AS 类型, a.length AS 占用字节数, COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 长度, isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS 小数位数, (case
                        WHEN a.isnullable=1 THEN
                        '1'else '0' end) AS 允许空, REPLACE(REPLACE(isnull(e.text,''),'(',''),')','') AS 默认值, isnull(g.[value],'') AS 字段说明
                    FROM syscolumns a
                    LEFT JOIN systypes b
                        ON a.xtype=b.xusertype
                    INNER JOIN sysobjects d
                        ON a.id=d.id
                            AND d.xtype='U'
                            AND d.name<>'dtproperties'
                    LEFT JOIN syscomments e
                        ON a.cdefault=e.id
                    LEFT JOIN sys.extended_properties g
                        ON a.id=g.major_id
                            AND a.colid = g.minor_id ) ak
                WHERE ak.表名='{tblName}'
            AND ak.主键=1
     
	 if ((select count(1) from #)<=0)
	 begin
	   RETURN
	 end
	 select * into #2 from deleted
	 select
	  @field=COALESCE(@field +',', '') + ak.字段名,
	  @field2=COALESCE(@field2 +'+', '') + ('''◆'''+'+cast('+ak.字段名+' as varchar(max))')
	 from
	   # ak
     select @field2 = '''''' + @field2	 
	 select @sql=N'insert into TblArchive(TableName,Extend1,Summary,Flag) select ''{tblName}'' f0,'''+@field+''' f1,'+@field2 +' f2,2 from #2'
	 EXEC sp_executesql @sql	 
	 drop table #
	 drop table #2
  end try
  begin catch
    select error_number() as error_number ,  
     error_message() as error_message,  
     error_state() as error_state,  
     error_severity() as error_severity 
  end catch
end
";
                    db.Execute(strSQL);
                }
                return true;
            }
            catch (Exception ex)
            {
                LogWrite.TxtExport_DateNow(LOG_ERR_FILE, $"DBMan.Deal SQLServerDBInit 1 Err:{ex.Message}");
            }
            return false;
        }

        private void MySQLDBInit()
        {
            try
            {
                if (this.DBType != 1)
                {
                    return;
                }
                //...Todo...
            }
            catch (Exception ex)
            {
                LogWrite.TxtExport_DateNow(LOG_ERR_FILE, $"DBMan.Deal MySQLDBInit Err:{ex.Message}");
            }
        }

    }
}
